"""
摄像头数据访问层
Enhanced repository with comprehensive CRUD operations, advanced querying,
pagination, filtering, and performance optimizations for camera data persistence.
"""
from typing import List, Optional, Dict, Any, Tuple, Union
from datetime import datetime, timedelta
from sqlalchemy.orm import Session, joinedload, selectinload
from sqlalchemy import (
    and_, or_, func, text, desc, asc, case, 
    select, update, delete, exists
)
from sqlalchemy.exc import IntegrityError, SQLAlchemyError
from models.camera import Camera, CameraStatus
from schemas.camera import CameraCreate, CameraUpdate, CameraFilters
from core.db_logging import log_database_operation, log_transaction
import structlog
import time
from contextlib import contextmanager

logger = structlog.get_logger(__name__)


class CameraRepositoryError(Exception):
    """摄像头仓储异常基类"""
    pass


class CameraNotFoundError(CameraRepositoryError):
    """摄像头未找到异常"""
    pass


class CameraDuplicateError(CameraRepositoryError):
    """摄像头重复异常"""
    pass


class CameraRepository:
    """
    摄像头数据访问类
    Enhanced repository with comprehensive CRUD operations, advanced querying,
    and performance optimizations for camera data persistence.
    """
    
    def __init__(self, db: Session):
        self.db = db
        self._query_cache = {}
        self._cache_ttl = 300  # 5分钟缓存
    
    @contextmanager
    def _handle_db_errors(self, operation: str, **context):
        """数据库错误处理上下文管理器"""
        start_time = time.time()
        try:
            yield
            duration = time.time() - start_time
            logger.debug(f"{operation}操作成功", duration=duration, **context)
        except IntegrityError as e:
            self.db.rollback()
            duration = time.time() - start_time
            logger.error(f"{operation}操作失败 - 完整性约束", 
                        duration=duration, error=str(e), **context)
            if "UNIQUE constraint failed" in str(e):
                raise CameraDuplicateError(f"摄像头名称已存在") from e
            raise CameraRepositoryError(f"{operation}失败: {str(e)}") from e
        except SQLAlchemyError as e:
            self.db.rollback()
            duration = time.time() - start_time
            logger.error(f"{operation}操作失败 - 数据库错误", 
                        duration=duration, error=str(e), **context)
            raise CameraRepositoryError(f"{operation}失败: {str(e)}") from e
        except Exception as e:
            self.db.rollback()
            duration = time.time() - start_time
            logger.error(f"{operation}操作失败 - 未知错误", 
                        duration=duration, error=str(e), **context)
            raise CameraRepositoryError(f"{operation}失败: {str(e)}") from e
    
    @log_database_operation("create", "cameras")
    def create(self, camera_data: CameraCreate) -> Camera:
        """
        创建摄像头记录
        
        Args:
            camera_data: 摄像头创建数据
            
        Returns:
            Camera: 创建的摄像头对象
            
        Raises:
            CameraDuplicateError: 摄像头名称已存在
            CameraRepositoryError: 其他数据库错误
        """
        with self._handle_db_errors("创建摄像头", name=camera_data.name):
            # 检查名称是否已存在
            existing = self.db.query(Camera).filter(Camera.name == camera_data.name).first()
            if existing:
                raise CameraDuplicateError(f"摄像头名称 '{camera_data.name}' 已存在")
            
            # 创建摄像头对象
            camera_dict = camera_data.model_dump()
            camera_dict['status'] = CameraStatus.OFFLINE  # 默认状态为离线
            camera_dict['stream_status'] = 'inactive'  # 默认流状态为非活跃
            camera_dict['reconnect_count'] = 0
            
            db_camera = Camera(**camera_dict)
            
            self.db.add(db_camera)
            self.db.commit()
            self.db.refresh(db_camera)
            
            # 清除相关缓存
            self._clear_cache()
            
            logger.info("摄像头创建成功", 
                       camera_id=db_camera.id, 
                       name=db_camera.name,
                       location=db_camera.location)
            return db_camera
    
    @log_database_operation("read", "cameras")
    def get_by_id(self, camera_id: int, include_relations: bool = False) -> Optional[Camera]:
        """
        根据ID获取摄像头
        
        Args:
            camera_id: 摄像头ID
            include_relations: 是否包含关联数据
            
        Returns:
            Optional[Camera]: 摄像头对象或None
        """
        try:
            query = self.db.query(Camera).filter(Camera.id == camera_id)
            
            if include_relations:
                # 预加载关联数据以避免N+1查询问题
                query = query.options(
                    selectinload(Camera.ai_tasks),
                    selectinload(Camera.ai_results),
                    selectinload(Camera.alerts)
                )
            
            camera = query.first()
            
            if camera:
                logger.debug("摄像头查询成功", camera_id=camera_id, name=camera.name)
            else:
                logger.debug("摄像头未找到", camera_id=camera_id)
                
            return camera
            
        except SQLAlchemyError as e:
            logger.error("根据ID查询摄像头失败", camera_id=camera_id, error=str(e))
            raise CameraRepositoryError(f"查询摄像头失败: {str(e)}") from e
    
    def get_by_name(self, name: str, include_relations: bool = False) -> Optional[Camera]:
        """
        根据名称获取摄像头
        
        Args:
            name: 摄像头名称
            include_relations: 是否包含关联数据
            
        Returns:
            Optional[Camera]: 摄像头对象或None
        """
        try:
            query = self.db.query(Camera).filter(Camera.name == name)
            
            if include_relations:
                query = query.options(
                    selectinload(Camera.ai_tasks),
                    selectinload(Camera.ai_results),
                    selectinload(Camera.alerts)
                )
            
            camera = query.first()
            
            if camera:
                logger.debug("摄像头查询成功", name=name, camera_id=camera.id)
            else:
                logger.debug("摄像头未找到", name=name)
                
            return camera
            
        except SQLAlchemyError as e:
            logger.error("根据名称查询摄像头失败", name=name, error=str(e))
            raise CameraRepositoryError(f"查询摄像头失败: {str(e)}") from e
    
    def get_by_wvp_device(self, device_id: str, channel_id: str = None) -> Optional[Camera]:
        """
        根据WVP设备ID和通道ID获取摄像头
        
        Args:
            device_id: WVP设备ID
            channel_id: WVP通道ID (可选)
            
        Returns:
            Optional[Camera]: 摄像头对象或None
        """
        try:
            query = self.db.query(Camera).filter(Camera.wvp_device_id == device_id)
            
            if channel_id:
                query = query.filter(Camera.wvp_channel_id == channel_id)
            
            camera = query.first()
            
            if camera:
                logger.debug("根据WVP设备查询摄像头成功", 
                           device_id=device_id, 
                           channel_id=channel_id,
                           camera_id=camera.id)
            
            return camera
            
        except SQLAlchemyError as e:
            logger.error("根据WVP设备查询摄像头失败", 
                        device_id=device_id, 
                        channel_id=channel_id, 
                        error=str(e))
            raise CameraRepositoryError(f"查询摄像头失败: {str(e)}") from e
    
    def get_list(
        self, 
        filters: CameraFilters, 
        sort_by: str = "created_at",
        sort_order: str = "desc",
        include_relations: bool = False
    ) -> Tuple[List[Camera], int]:
        """
        获取摄像头列表 - 支持高级筛选、排序和分页
        
        Args:
            filters: 筛选条件
            sort_by: 排序字段
            sort_order: 排序方向 (asc/desc)
            include_relations: 是否包含关联数据
            
        Returns:
            Tuple[List[Camera], int]: (摄像头列表, 总数)
        """
        try:
            # 构建基础查询
            query = self.db.query(Camera)
            
            # 应用筛选条件
            query = self._apply_filters(query, filters)
            
            # 获取总数 (在排序和分页之前)
            total = query.count()
            
            # 应用排序
            query = self._apply_sorting(query, sort_by, sort_order)
            
            # 应用分页
            offset = (filters.page - 1) * filters.size
            query = query.offset(offset).limit(filters.size)
            
            # 预加载关联数据
            if include_relations:
                query = query.options(
                    selectinload(Camera.ai_tasks),
                    selectinload(Camera.ai_results),
                    selectinload(Camera.alerts)
                )
            
            cameras = query.all()
            
            logger.debug("摄像头列表查询成功", 
                        total=total, 
                        returned=len(cameras),
                        page=filters.page,
                        size=filters.size)
            
            return cameras, total
            
        except SQLAlchemyError as e:
            logger.error("获取摄像头列表失败", error=str(e))
            raise CameraRepositoryError(f"获取摄像头列表失败: {str(e)}") from e
    
    def _apply_filters(self, query, filters: CameraFilters):
        """应用筛选条件"""
        if filters.name:
            query = query.filter(Camera.name.ilike(f"%{filters.name}%"))
        
        if filters.location:
            query = query.filter(Camera.location.ilike(f"%{filters.location}%"))
        
        if filters.status:
            query = query.filter(Camera.status == filters.status)
        
        if filters.ai_enabled is not None:
            query = query.filter(Camera.ai_enabled == filters.ai_enabled)
        
        # 扩展筛选条件
        if hasattr(filters, 'wvp_device_id') and filters.wvp_device_id:
            query = query.filter(Camera.wvp_device_id == filters.wvp_device_id)
        
        if hasattr(filters, 'priority_min') and filters.priority_min:
            query = query.filter(Camera.priority_level >= filters.priority_min)
        
        if hasattr(filters, 'priority_max') and filters.priority_max:
            query = query.filter(Camera.priority_level <= filters.priority_max)
        
        if hasattr(filters, 'created_after') and filters.created_after:
            query = query.filter(Camera.created_at >= filters.created_after)
        
        if hasattr(filters, 'created_before') and filters.created_before:
            query = query.filter(Camera.created_at <= filters.created_before)
        
        return query
    
    def _apply_sorting(self, query, sort_by: str, sort_order: str):
        """应用排序"""
        # 验证排序字段
        valid_sort_fields = [
            'id', 'name', 'location', 'status', 'ai_enabled', 
            'priority_level', 'created_at', 'updated_at',
            'longitude', 'latitude', 'processing_fps'
        ]
        
        if sort_by not in valid_sort_fields:
            sort_by = 'created_at'
        
        # 获取排序字段
        sort_field = getattr(Camera, sort_by)
        
        # 应用排序方向
        if sort_order.lower() == 'asc':
            query = query.order_by(asc(sort_field))
        else:
            query = query.order_by(desc(sort_field))
        
        return query
    
    @log_database_operation("update", "cameras")
    def update(self, camera_id: int, camera_data: CameraUpdate) -> Optional[Camera]:
        """
        更新摄像头信息
        
        Args:
            camera_id: 摄像头ID
            camera_data: 更新数据
            
        Returns:
            Optional[Camera]: 更新后的摄像头对象或None
            
        Raises:
            CameraNotFoundError: 摄像头不存在
            CameraDuplicateError: 名称冲突
            CameraRepositoryError: 其他数据库错误
        """
        with self._handle_db_errors("更新摄像头", camera_id=camera_id):
            db_camera = self.get_by_id(camera_id)
            if not db_camera:
                raise CameraNotFoundError(f"摄像头 ID {camera_id} 不存在")
            
            # 获取更新数据
            update_data = camera_data.model_dump(exclude_unset=True)
            
            # 检查名称冲突
            if 'name' in update_data and update_data['name'] != db_camera.name:
                existing = self.db.query(Camera).filter(
                    and_(
                        Camera.name == update_data['name'],
                        Camera.id != camera_id
                    )
                ).first()
                if existing:
                    raise CameraDuplicateError(f"摄像头名称 '{update_data['name']}' 已存在")
            
            # 记录更新前的状态
            old_values = {}
            for field in update_data.keys():
                if hasattr(db_camera, field):
                    old_values[field] = getattr(db_camera, field)
            
            # 应用更新
            for field, value in update_data.items():
                if hasattr(db_camera, field):
                    setattr(db_camera, field, value)
            
            # 更新时间戳
            db_camera.updated_at = datetime.utcnow()
            
            self.db.commit()
            self.db.refresh(db_camera)
            
            # 清除相关缓存
            self._clear_cache()
            
            logger.info("摄像头更新成功", 
                       camera_id=camera_id,
                       name=db_camera.name,
                       updated_fields=list(update_data.keys()),
                       old_values=old_values)
            
            return db_camera
    
    def update_status(self, camera_id: int, status: CameraStatus) -> Optional[Camera]:
        """更新摄像头状态"""
        try:
            db_camera = self.get_by_id(camera_id)
            if not db_camera:
                return None
            
            db_camera.status = status
            self.db.commit()
            self.db.refresh(db_camera)
            
            logger.info("摄像头状态更新成功", camera_id=camera_id, status=status)
            return db_camera
            
        except Exception as e:
            self.db.rollback()
            logger.error("摄像头状态更新失败", camera_id=camera_id, exc_info=e)
            raise
    
    @log_database_operation("delete", "cameras")
    def delete(self, camera_id: int, force: bool = False) -> bool:
        """
        删除摄像头记录
        
        Args:
            camera_id: 摄像头ID
            force: 是否强制删除 (忽略依赖关系检查)
            
        Returns:
            bool: 删除是否成功
            
        Raises:
            CameraNotFoundError: 摄像头不存在
            CameraRepositoryError: 删除失败或存在依赖关系
        """
        with self._handle_db_errors("删除摄像头", camera_id=camera_id):
            db_camera = self.get_by_id(camera_id, include_relations=True)
            if not db_camera:
                raise CameraNotFoundError(f"摄像头 ID {camera_id} 不存在")
            
            # 检查依赖关系
            if not force:
                dependencies = self._check_dependencies(db_camera)
                if dependencies:
                    raise CameraRepositoryError(
                        f"无法删除摄像头，存在依赖关系: {', '.join(dependencies)}"
                    )
            
            # 记录删除信息
            camera_info = {
                'id': db_camera.id,
                'name': db_camera.name,
                'location': db_camera.location,
                'status': db_camera.status
            }
            
            self.db.delete(db_camera)
            self.db.commit()
            
            # 清除相关缓存
            self._clear_cache()
            
            logger.info("摄像头删除成功", **camera_info)
            return True
    
    def _check_dependencies(self, camera: Camera) -> List[str]:
        """检查摄像头的依赖关系"""
        dependencies = []
        
        # 检查AI任务
        if hasattr(camera, 'ai_tasks') and camera.ai_tasks:
            active_tasks = [task for task in camera.ai_tasks if task.status in ['pending', 'running']]
            if active_tasks:
                dependencies.append(f"{len(active_tasks)} 个活跃AI任务")
        
        # 检查未解决的告警
        if hasattr(camera, 'alerts') and camera.alerts:
            unresolved_alerts = [alert for alert in camera.alerts if alert.status != 'resolved']
            if unresolved_alerts:
                dependencies.append(f"{len(unresolved_alerts)} 个未解决告警")
        
        return dependencies
    
    def get_online_cameras(self, include_relations: bool = False) -> List[Camera]:
        """
        获取在线摄像头列表
        
        Args:
            include_relations: 是否包含关联数据
            
        Returns:
            List[Camera]: 在线摄像头列表
        """
        try:
            query = self.db.query(Camera).filter(Camera.status == CameraStatus.ONLINE)
            
            if include_relations:
                query = query.options(
                    selectinload(Camera.ai_tasks),
                    selectinload(Camera.ai_results),
                    selectinload(Camera.alerts)
                )
            
            cameras = query.all()
            
            logger.debug("在线摄像头查询成功", count=len(cameras))
            return cameras
            
        except SQLAlchemyError as e:
            logger.error("获取在线摄像头失败", error=str(e))
            raise CameraRepositoryError(f"获取在线摄像头失败: {str(e)}") from e
    
    def get_ai_enabled_cameras(self, status_filter: str = "online") -> List[Camera]:
        """
        获取启用AI分析的摄像头列表
        
        Args:
            status_filter: 状态筛选 ("online", "all", "offline")
            
        Returns:
            List[Camera]: 启用AI的摄像头列表
        """
        try:
            query = self.db.query(Camera).filter(Camera.ai_enabled == True)
            
            if status_filter == "online":
                query = query.filter(Camera.status == CameraStatus.ONLINE)
            elif status_filter == "offline":
                query = query.filter(Camera.status != CameraStatus.ONLINE)
            # status_filter == "all" 时不添加状态筛选
            
            cameras = query.all()
            
            logger.debug("AI启用摄像头查询成功", 
                        count=len(cameras), 
                        status_filter=status_filter)
            return cameras
            
        except SQLAlchemyError as e:
            logger.error("获取AI启用摄像头失败", error=str(e))
            raise CameraRepositoryError(f"获取AI启用摄像头失败: {str(e)}") from e
    
    def get_cameras_by_location(self, location: str, exact_match: bool = False) -> List[Camera]:
        """
        根据位置获取摄像头列表
        
        Args:
            location: 位置关键词
            exact_match: 是否精确匹配
            
        Returns:
            List[Camera]: 摄像头列表
        """
        try:
            if exact_match:
                query = self.db.query(Camera).filter(Camera.location == location)
            else:
                query = self.db.query(Camera).filter(
                    Camera.location.ilike(f"%{location}%")
                )
            
            cameras = query.all()
            
            logger.debug("按位置查询摄像头成功", 
                        location=location, 
                        exact_match=exact_match,
                        count=len(cameras))
            return cameras
            
        except SQLAlchemyError as e:
            logger.error("按位置查询摄像头失败", location=location, error=str(e))
            raise CameraRepositoryError(f"按位置查询摄像头失败: {str(e)}") from e
    
    def get_cameras_by_coordinates(
        self, 
        min_lng: float, 
        max_lng: float, 
        min_lat: float, 
        max_lat: float
    ) -> List[Camera]:
        """
        根据坐标范围获取摄像头列表
        
        Args:
            min_lng: 最小经度
            max_lng: 最大经度
            min_lat: 最小纬度
            max_lat: 最大纬度
            
        Returns:
            List[Camera]: 范围内的摄像头列表
        """
        try:
            cameras = self.db.query(Camera).filter(
                and_(
                    Camera.longitude >= min_lng,
                    Camera.longitude <= max_lng,
                    Camera.latitude >= min_lat,
                    Camera.latitude <= max_lat
                )
            ).all()
            
            logger.debug("按坐标范围查询摄像头成功", 
                        lng_range=(min_lng, max_lng),
                        lat_range=(min_lat, max_lat),
                        count=len(cameras))
            return cameras
            
        except SQLAlchemyError as e:
            logger.error("按坐标范围查询摄像头失败", error=str(e))
            raise CameraRepositoryError(f"按坐标范围查询摄像头失败: {str(e)}") from e
    
    def get_cameras_by_priority(self, min_priority: int = 1, max_priority: int = 10) -> List[Camera]:
        """
        根据优先级范围获取摄像头列表
        
        Args:
            min_priority: 最小优先级
            max_priority: 最大优先级
            
        Returns:
            List[Camera]: 摄像头列表
        """
        try:
            cameras = self.db.query(Camera).filter(
                and_(
                    Camera.priority_level >= min_priority,
                    Camera.priority_level <= max_priority
                )
            ).order_by(desc(Camera.priority_level)).all()
            
            logger.debug("按优先级查询摄像头成功", 
                        priority_range=(min_priority, max_priority),
                        count=len(cameras))
            return cameras
            
        except SQLAlchemyError as e:
            logger.error("按优先级查询摄像头失败", error=str(e))
            raise CameraRepositoryError(f"按优先级查询摄像头失败: {str(e)}") from e
    
    def get_statistics(self, detailed: bool = False) -> Dict[str, Any]:
        """
        获取摄像头统计信息
        
        Args:
            detailed: 是否返回详细统计
            
        Returns:
            Dict[str, Any]: 统计信息
        """
        try:
            # 基础统计
            total = self.db.query(Camera).count()
            online = self.db.query(Camera).filter(Camera.status == CameraStatus.ONLINE).count()
            offline = self.db.query(Camera).filter(Camera.status == CameraStatus.OFFLINE).count()
            error = self.db.query(Camera).filter(Camera.status == CameraStatus.ERROR).count()
            maintenance = self.db.query(Camera).filter(Camera.status == CameraStatus.MAINTENANCE).count()
            ai_enabled = self.db.query(Camera).filter(Camera.ai_enabled == True).count()
            
            stats = {
                "total": total,
                "online": online,
                "offline": offline,
                "error": error,
                "maintenance": maintenance,
                "ai_enabled": ai_enabled,
                "online_rate": round(online / total * 100, 2) if total > 0 else 0,
                "ai_enabled_rate": round(ai_enabled / total * 100, 2) if total > 0 else 0,
                "timestamp": datetime.utcnow().isoformat()
            }
            
            if detailed:
                # 详细统计
                stats.update(self._get_detailed_statistics())
            
            logger.debug("摄像头统计信息查询成功", total=total, online=online)
            return stats
            
        except SQLAlchemyError as e:
            logger.error("获取摄像头统计信息失败", error=str(e))
            raise CameraRepositoryError(f"获取统计信息失败: {str(e)}") from e
    
    def _get_detailed_statistics(self) -> Dict[str, Any]:
        """获取详细统计信息"""
        try:
            # 按位置统计
            location_stats = self.db.query(
                Camera.location,
                func.count(Camera.id).label('count')
            ).group_by(Camera.location).all()
            
            # 按优先级统计
            priority_stats = self.db.query(
                Camera.priority_level,
                func.count(Camera.id).label('count')
            ).group_by(Camera.priority_level).order_by(Camera.priority_level).all()
            
            # 按创建时间统计 (最近30天)
            thirty_days_ago = datetime.utcnow() - timedelta(days=30)
            recent_cameras = self.db.query(Camera).filter(
                Camera.created_at >= thirty_days_ago
            ).count()
            
            # 流状态统计
            stream_stats = self.db.query(
                Camera.stream_status,
                func.count(Camera.id).label('count')
            ).group_by(Camera.stream_status).all()
            
            # 平均处理帧率
            avg_fps = self.db.query(func.avg(Camera.processing_fps)).scalar() or 0
            
            return {
                "location_distribution": [
                    {"location": loc or "未设置", "count": count} 
                    for loc, count in location_stats
                ],
                "priority_distribution": [
                    {"priority": priority, "count": count} 
                    for priority, count in priority_stats
                ],
                "recent_additions": recent_cameras,
                "stream_status_distribution": [
                    {"status": status or "unknown", "count": count} 
                    for status, count in stream_stats
                ],
                "average_processing_fps": round(float(avg_fps), 2)
            }
            
        except SQLAlchemyError as e:
            logger.error("获取详细统计信息失败", error=str(e))
            return {}
    
    @log_transaction("batch_update_status", ["cameras"])
    def batch_update_status(self, camera_ids: List[int], status: CameraStatus) -> int:
        """
        批量更新摄像头状态
        
        Args:
            camera_ids: 摄像头ID列表
            status: 目标状态
            
        Returns:
            int: 更新的记录数
            
        Raises:
            CameraRepositoryError: 更新失败
        """
        with self._handle_db_errors("批量更新状态", camera_ids=camera_ids, status=status):
            if not camera_ids:
                return 0
            
            # 验证摄像头是否存在
            existing_count = self.db.query(Camera).filter(
                Camera.id.in_(camera_ids)
            ).count()
            
            if existing_count != len(camera_ids):
                logger.warning("部分摄像头不存在", 
                             requested=len(camera_ids), 
                             existing=existing_count)
            
            # 批量更新
            updated_count = self.db.query(Camera).filter(
                Camera.id.in_(camera_ids)
            ).update(
                {
                    "status": status,
                    "updated_at": datetime.utcnow()
                },
                synchronize_session=False
            )
            
            self.db.commit()
            
            # 清除相关缓存
            self._clear_cache()
            
            logger.info("批量更新摄像头状态成功", 
                       count=updated_count, 
                       status=status,
                       camera_ids=camera_ids[:10])  # 只记录前10个ID
            
            return updated_count
    
    def batch_update_ai_enabled(self, camera_ids: List[int], ai_enabled: bool) -> int:
        """
        批量更新AI启用状态
        
        Args:
            camera_ids: 摄像头ID列表
            ai_enabled: AI启用状态
            
        Returns:
            int: 更新的记录数
        """
        with self._handle_db_errors("批量更新AI状态", camera_ids=camera_ids, ai_enabled=ai_enabled):
            if not camera_ids:
                return 0
            
            updated_count = self.db.query(Camera).filter(
                Camera.id.in_(camera_ids)
            ).update(
                {
                    "ai_enabled": ai_enabled,
                    "updated_at": datetime.utcnow()
                },
                synchronize_session=False
            )
            
            self.db.commit()
            
            # 清除相关缓存
            self._clear_cache()
            
            logger.info("批量更新AI启用状态成功", 
                       count=updated_count, 
                       ai_enabled=ai_enabled)
            
            return updated_count
    
    def batch_delete(self, camera_ids: List[int], force: bool = False) -> int:
        """
        批量删除摄像头
        
        Args:
            camera_ids: 摄像头ID列表
            force: 是否强制删除
            
        Returns:
            int: 删除的记录数
        """
        with self._handle_db_errors("批量删除摄像头", camera_ids=camera_ids):
            if not camera_ids:
                return 0
            
            # 如果不是强制删除，检查依赖关系
            if not force:
                cameras_with_deps = []
                for camera_id in camera_ids:
                    camera = self.get_by_id(camera_id, include_relations=True)
                    if camera:
                        deps = self._check_dependencies(camera)
                        if deps:
                            cameras_with_deps.append((camera_id, deps))
                
                if cameras_with_deps:
                    dep_info = [f"ID {cid}: {', '.join(deps)}" for cid, deps in cameras_with_deps]
                    raise CameraRepositoryError(
                        f"无法删除摄像头，存在依赖关系: {'; '.join(dep_info)}"
                    )
            
            # 执行批量删除
            deleted_count = self.db.query(Camera).filter(
                Camera.id.in_(camera_ids)
            ).delete(synchronize_session=False)
            
            self.db.commit()
            
            # 清除相关缓存
            self._clear_cache()
            
            logger.info("批量删除摄像头成功", count=deleted_count)
            return deleted_count    # 缓存管理方法
            
    def _clear_cache(self):
        """清除查询缓存"""
        self._query_cache.clear()
        logger.debug("查询缓存已清除")
    
    def _get_cached_result(self, cache_key: str):
        """获取缓存结果"""
        if cache_key in self._query_cache:
            cached_data, timestamp = self._query_cache[cache_key]
            if time.time() - timestamp < self._cache_ttl:
                logger.debug("使用缓存结果", cache_key=cache_key)
                return cached_data
            else:
                # 缓存过期，删除
                del self._query_cache[cache_key]
        return None
    
    def _set_cached_result(self, cache_key: str, data):
        """设置缓存结果"""
        self._query_cache[cache_key] = (data, time.time())
        logger.debug("缓存结果已保存", cache_key=cache_key)
    
    # 高级查询方法
    def search_cameras(
        self, 
        keyword: str, 
        search_fields: List[str] = None,
        limit: int = 50
    ) -> List[Camera]:
        """
        搜索摄像头 - 支持多字段模糊搜索
        
        Args:
            keyword: 搜索关键词
            search_fields: 搜索字段列表
            limit: 结果限制
            
        Returns:
            List[Camera]: 搜索结果
        """
        try:
            if not keyword.strip():
                return []
            
            if search_fields is None:
                search_fields = ['name', 'location', 'description', 'wvp_device_id']
            
            # 构建搜索条件
            search_conditions = []
            for field in search_fields:
                if hasattr(Camera, field):
                    field_attr = getattr(Camera, field)
                    search_conditions.append(field_attr.ilike(f"%{keyword}%"))
            
            if not search_conditions:
                return []
            
            # 执行搜索
            cameras = self.db.query(Camera).filter(
                or_(*search_conditions)
            ).limit(limit).all()
            
            logger.debug("摄像头搜索完成", 
                        keyword=keyword, 
                        fields=search_fields,
                        results=len(cameras))
            
            return cameras
            
        except SQLAlchemyError as e:
            logger.error("摄像头搜索失败", keyword=keyword, error=str(e))
            raise CameraRepositoryError(f"搜索失败: {str(e)}") from e
    
    def get_cameras_with_recent_activity(self, hours: int = 24) -> List[Camera]:
        """
        获取最近有活动的摄像头
        
        Args:
            hours: 时间范围（小时）
            
        Returns:
            List[Camera]: 有活动的摄像头列表
        """
        try:
            cutoff_time = datetime.utcnow() - timedelta(hours=hours)
            
            cameras = self.db.query(Camera).filter(
                or_(
                    Camera.last_frame_time >= cutoff_time,
                    Camera.updated_at >= cutoff_time
                )
            ).order_by(desc(Camera.last_frame_time)).all()
            
            logger.debug("最近活动摄像头查询完成", 
                        hours=hours, 
                        count=len(cameras))
            
            return cameras
            
        except SQLAlchemyError as e:
            logger.error("查询最近活动摄像头失败", error=str(e))
            raise CameraRepositoryError(f"查询失败: {str(e)}") from e
    
    def get_cameras_by_algorithm(self, algorithm: str) -> List[Camera]:
        """
        根据AI算法获取摄像头列表
        
        Args:
            algorithm: AI算法名称
            
        Returns:
            List[Camera]: 启用指定算法的摄像头列表
        """
        try:
            cameras = self.db.query(Camera).filter(
                and_(
                    Camera.ai_enabled == True,
                    Camera.ai_algorithms.op('->')(algorithm).isnot(None)
                )
            ).all()
            
            logger.debug("按算法查询摄像头完成", 
                        algorithm=algorithm, 
                        count=len(cameras))
            
            return cameras
            
        except SQLAlchemyError as e:
            logger.error("按算法查询摄像头失败", algorithm=algorithm, error=str(e))
            raise CameraRepositoryError(f"查询失败: {str(e)}") from e
    
    def update_performance_metrics(
        self, 
        camera_id: int, 
        metrics: Dict[str, Any]
    ) -> Optional[Camera]:
        """
        更新摄像头性能指标
        
        Args:
            camera_id: 摄像头ID
            metrics: 性能指标数据
            
        Returns:
            Optional[Camera]: 更新后的摄像头对象
        """
        with self._handle_db_errors("更新性能指标", camera_id=camera_id):
            db_camera = self.get_by_id(camera_id)
            if not db_camera:
                raise CameraNotFoundError(f"摄像头 ID {camera_id} 不存在")
            
            # 合并现有指标
            current_metrics = db_camera.performance_metrics or {}
            current_metrics.update(metrics)
            current_metrics['updated_at'] = datetime.utcnow().isoformat()
            
            db_camera.performance_metrics = current_metrics
            db_camera.updated_at = datetime.utcnow()
            
            self.db.commit()
            self.db.refresh(db_camera)
            
            logger.debug("性能指标更新成功", 
                        camera_id=camera_id,
                        metrics_keys=list(metrics.keys()))
            
            return db_camera
    
    def update_last_frame_time(self, camera_id: int, frame_time: datetime = None) -> bool:
        """
        更新摄像头最后帧时间
        
        Args:
            camera_id: 摄像头ID
            frame_time: 帧时间 (默认为当前时间)
            
        Returns:
            bool: 更新是否成功
        """
        try:
            if frame_time is None:
                frame_time = datetime.utcnow()
            
            updated_count = self.db.query(Camera).filter(
                Camera.id == camera_id
            ).update(
                {
                    "last_frame_time": frame_time,
                    "updated_at": datetime.utcnow()
                },
                synchronize_session=False
            )
            
            self.db.commit()
            
            if updated_count > 0:
                logger.debug("最后帧时间更新成功", camera_id=camera_id)
                return True
            else:
                logger.warning("摄像头不存在，无法更新帧时间", camera_id=camera_id)
                return False
                
        except SQLAlchemyError as e:
            self.db.rollback()
            logger.error("更新最后帧时间失败", camera_id=camera_id, error=str(e))
            raise CameraRepositoryError(f"更新失败: {str(e)}") from e
    
    def increment_reconnect_count(self, camera_id: int) -> Optional[Camera]:
        """
        增加摄像头重连次数
        
        Args:
            camera_id: 摄像头ID
            
        Returns:
            Optional[Camera]: 更新后的摄像头对象
        """
        with self._handle_db_errors("增加重连次数", camera_id=camera_id):
            db_camera = self.get_by_id(camera_id)
            if not db_camera:
                raise CameraNotFoundError(f"摄像头 ID {camera_id} 不存在")
            
            db_camera.reconnect_count = (db_camera.reconnect_count or 0) + 1
            db_camera.updated_at = datetime.utcnow()
            
            self.db.commit()
            self.db.refresh(db_camera)
            
            logger.debug("重连次数增加成功", 
                        camera_id=camera_id,
                        reconnect_count=db_camera.reconnect_count)
            
            return db_camera
    
    def reset_reconnect_count(self, camera_id: int) -> Optional[Camera]:
        """
        重置摄像头重连次数
        
        Args:
            camera_id: 摄像头ID
            
        Returns:
            Optional[Camera]: 更新后的摄像头对象
        """
        with self._handle_db_errors("重置重连次数", camera_id=camera_id):
            db_camera = self.get_by_id(camera_id)
            if not db_camera:
                raise CameraNotFoundError(f"摄像头 ID {camera_id} 不存在")
            
            db_camera.reconnect_count = 0
            db_camera.updated_at = datetime.utcnow()
            
            self.db.commit()
            self.db.refresh(db_camera)
            
            logger.debug("重连次数重置成功", camera_id=camera_id)
            return db_camera
    
    def update_stream_status(
        self, 
        camera_id: int, 
        stream_status: str,
        stream_url: str = None,
        error_message: str = None
    ) -> Optional[Camera]:
        """
        更新摄像头流状态
        
        Args:
            camera_id: 摄像头ID
            stream_status: 流状态
            stream_url: 流URL
            error_message: 错误消息
            
        Returns:
            Optional[Camera]: 更新后的摄像头对象
        """
        with self._handle_db_errors("更新流状态", camera_id=camera_id):
            db_camera = self.get_by_id(camera_id)
            if not db_camera:
                raise CameraNotFoundError(f"摄像头 ID {camera_id} 不存在")
            
            db_camera.stream_status = stream_status
            if stream_url is not None:
                db_camera.stream_url = stream_url
            if error_message is not None:
                db_camera.last_error = error_message
            db_camera.updated_at = datetime.utcnow()
            
            self.db.commit()
            self.db.refresh(db_camera)
            
            logger.debug("流状态更新成功", 
                        camera_id=camera_id,
                        stream_status=stream_status)
            
            return db_camera
    
    def exists_by_name(self, name: str, exclude_id: int = None) -> bool:
        """
        检查摄像头名称是否存在
        
        Args:
            name: 摄像头名称
            exclude_id: 排除的摄像头ID
            
        Returns:
            bool: 是否存在
        """
        try:
            query = self.db.query(Camera).filter(Camera.name == name)
            
            if exclude_id is not None:
                query = query.filter(Camera.id != exclude_id)
            
            exists = query.first() is not None
            
            logger.debug("名称存在性检查完成", name=name, exists=exists)
            return exists
            
        except SQLAlchemyError as e:
            logger.error("检查名称存在性失败", name=name, error=str(e))
            raise CameraRepositoryError(f"检查失败: {str(e)}") from e
    
    def get_count_by_status(self, status: CameraStatus) -> int:
        """
        获取指定状态的摄像头数量
        
        Args:
            status: 摄像头状态
            
        Returns:
            int: 摄像头数量
        """
        try:
            count = self.db.query(Camera).filter(Camera.status == status).count()
            
            logger.debug("状态计数查询完成", status=status, count=count)
            return count
            
        except SQLAlchemyError as e:
            logger.error("状态计数查询失败", status=status, error=str(e))
            raise CameraRepositoryError(f"查询失败: {str(e)}") from e
    
    def get_health_summary(self) -> Dict[str, Any]:
        """
        获取摄像头健康状况摘要
        
        Returns:
            Dict[str, Any]: 健康状况摘要
        """
        try:
            # 基础统计
            total = self.db.query(Camera).count()
            online = self.db.query(Camera).filter(Camera.status == CameraStatus.ONLINE).count()
            error = self.db.query(Camera).filter(Camera.status == CameraStatus.ERROR).count()
            
            # 高重连次数的摄像头
            high_reconnect = self.db.query(Camera).filter(
                Camera.reconnect_count > 10
            ).count()
            
            # 长时间无活动的摄像头
            one_hour_ago = datetime.utcnow() - timedelta(hours=1)
            inactive = self.db.query(Camera).filter(
                or_(
                    Camera.last_frame_time < one_hour_ago,
                    Camera.last_frame_time.is_(None)
                )
            ).count()
            
            health_score = 0
            if total > 0:
                health_score = ((online - error - high_reconnect) / total) * 100
                health_score = max(0, min(100, health_score))  # 限制在0-100之间
            
            summary = {
                "total_cameras": total,
                "online_cameras": online,
                "error_cameras": error,
                "high_reconnect_cameras": high_reconnect,
                "inactive_cameras": inactive,
                "health_score": round(health_score, 2),
                "timestamp": datetime.utcnow().isoformat()
            }
            
            logger.debug("健康状况摘要生成完成", health_score=health_score)
            return summary
            
        except SQLAlchemyError as e:
            logger.error("获取健康状况摘要失败", error=str(e))
            raise CameraRepositoryError(f"获取摘要失败: {str(e)}") from e


# 工厂函数
def create_camera_repository(db: Session) -> CameraRepository:
    """
    创建摄像头仓储实例
    
    Args:
        db: 数据库会话
        
    Returns:
        CameraRepository: 摄像头仓储实例
    """
    return CameraRepository(db)